package com.jxc.jxc01.hch.dao.Impl;


import com.jxc.jxc01.hch.dao.CommodityDao;
import com.jxc.jxc01.hch.entity.Commodity;
import com.jxc.jxc01.hch.entity.JDBCConn;
import com.jxc.jxc01.hch.entity.PageHelper;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class CommodityDaoImpl implements CommodityDao {
    /**
     * 查询商品资料
     *
     * @return 当前商品资料，页码页容等
     */
    @Override
    public PageHelper<Commodity> list(PageHelper<Commodity> pageHelper) {
        List<Commodity> list = new ArrayList<>();
        try {
            //加载驱动类
            JDBCConn jdbcConn = new JDBCConn();
            //编译sql语句
            String sql ="select count(*) from commodity";
            PreparedStatement stat = jdbcConn.connect(sql);
            ResultSet rs = stat.executeQuery();
            int count=0;
            if (rs.next()) {
                count =rs.getInt("count(*)");
            }
            //将总页数存入pageHelper中
            pageHelper.setTotal(count);
            String sql1="select * from commodity limit ?,?";
            PreparedStatement stat1 = jdbcConn.connect(sql1);
            stat1.setInt(1,
                    (pageHelper.getPageNum()-1)*pageHelper.getPageSize());
            stat1.setInt(2,pageHelper.getPageSize());
            ResultSet rs1 = stat1.executeQuery();
            while (rs1.next()){
                Commodity commodity = new Commodity();
                int id = rs1.getInt("id");
                String name = rs1.getString("commodityname");
                int classify = rs1.getInt("classify");
                String specs =rs1.getString("specs");
                double price = rs1.getDouble("price");
                String creationtime = rs1.getString("creationtime");
                String updatetime = rs1.getString("updatetime");
                commodity.setId(id);
                commodity.setClassify(classify);
                commodity.setName(name);
                commodity.setPrice(price);
                commodity.setSpecs(specs);
                commodity.setCreationTime(creationtime);
                commodity.setUpdateTime(updatetime);
                list.add(commodity);
            }
            pageHelper.setData(list);
            rs1.close();
            stat1.close();
            rs.close();
            stat.close();
        }catch (Exception e){
            throw new RuntimeException(e);
        }

        return pageHelper;
    }

    /**
     * 修改商品资料
     * @param commodity
     * @return 0代表修改失败，1代表修改成功
     */
    @Override
    public int update(Commodity commodity) {
            int up=0;

            System.out.println(1);
            //加载驱动类
        try {
            JDBCConn jdbcConn = new JDBCConn();
            //编译sql语句
            String sql = "update commodity set commodityname=?,classify=?,specs=?,price=?,updatetime=? where id=?";
            PreparedStatement stat = jdbcConn.connect(sql);
            stat.setString(1,commodity.getName());
            stat.setInt(2,commodity.getClassify());
            stat.setString(3,commodity.getSpecs());
            stat.setDouble(4,commodity.getPrice());
            stat.setString(5,commodity.getUpdateTime());
            stat.setInt(6,commodity.getId());
            up = stat.executeUpdate();
            stat.close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

        return up;
    }

    /**
     * 删除商品资料
     * @param id
     * @return 0代表删除失败，1代表删除成功
     */
    @Override
    public int delete(int id) {
        int deleteCount=0;
        try {

            JDBCConn jdbcConn = new JDBCConn();
            String sql ="delete from commodity where id=?";
            PreparedStatement stat = jdbcConn.connect(sql);
              stat.setInt(1,id);
            deleteCount=stat.executeUpdate();
            stat.close();
        }catch (Exception e){
            throw new RuntimeException(e);
        }
        return deleteCount;
    }

    /**
     * 添加商品资料
     * @param commodity
     * @return
     */
    @Override
    public boolean insert(Commodity commodity) {
        boolean f =false;
        try {

            //加载驱动类
            JDBCConn jdbcConn = new JDBCConn();
            //编译sql语句
            String sql = "insert into commodity(commodityname,classify,specs,price,creationtime) values (?,?,?,?,?)";
            PreparedStatement stat = jdbcConn.connect(sql);
            stat.setString(1,commodity.getName());
            stat.setInt(2,commodity.getClassify());
            stat.setString(3,commodity.getSpecs());
            stat.setDouble(4,commodity.getPrice());
            stat.setString(5,commodity.getCreationTime());
            stat.executeUpdate();
            f=true;
            stat.close();
        }catch (Exception e){
            throw new RuntimeException(e);
        }

        return f;
    }

    /**
     * 模糊查询商品资料
     * @param name
     * @return
     */
    @Override
    public PageHelper<Commodity> query(String name,PageHelper<Commodity> pageHelper) {
        List<Commodity> list = new ArrayList<>();
        try {

            JDBCConn jdbcConn = new JDBCConn();
            //编译sql语句
            String sql = "select * from commodity where commodityname like ? limit ?,?";
            PreparedStatement stat = jdbcConn.connect(sql);
            stat.setString(1,"%"+name+"%");
            stat.setInt(2,
                    (pageHelper.getPageNum()-1)*pageHelper.getPageSize());
            stat.setInt(3,pageHelper.getPageSize());
            ResultSet rs = stat.executeQuery();
            while (rs.next()){
                Commodity commodity = new Commodity();
                int id = rs.getInt("id");
                String commodityname = rs.getString("commodityname");
                int classify = rs.getInt("classify");
                String specs =rs.getString("specs");
                double price = rs.getDouble("price");
                String creationtime = rs.getString("creationtime");
                String updatetime = rs.getString("updatetime");
                commodity.setId(id);
                commodity.setClassify(classify);
                commodity.setName(commodityname);
                commodity.setPrice(price);
                commodity.setSpecs(specs);
                commodity.setCreationTime(creationtime);
                commodity.setUpdateTime(updatetime);
                list.add(commodity);
            }
            pageHelper.setData(list);
            String sql1 = "select count(*) from commodity where commodityname like ?";
            PreparedStatement stat1 =jdbcConn.connect(sql1);
            stat1.setString(1,"%"+name+"%");
            ResultSet rs1 = stat1.executeQuery();
            int total=0;
            if(rs1.next()){
                total =rs1.getInt("count(*)");

            }
            pageHelper.setTotal(total);
            rs.close();
            stat.close();
        }catch (Exception e){
            throw new RuntimeException(e);
        }
        return pageHelper;
    }
}
